
insert overwrite table jms_dm.dm_terminal_sign_summary_send_new_dt partition(dt)
--末端时效规划签收维度汇总
select   order_source_name --订单来源名称
        ,goods_type_code --物品类型编码
        ,end_manage_region_code --末端网点所属管理大区编码
        ,max(end_manage_region_name) as end_manage_region_name --末端网点所属管理大区名称
        ,end_agent_code --末端网点所属代理区编码
        ,max(end_agent_name) as end_agent_name --末端网点所属代理区名称
        ,max(end_provider_id) as end_provider_id --末端网点所属省份id
        ,max(end_provider_name) as end_provider_name --末端网点所属省份名称
        ,max(end_city_id) as end_city_id --末端网点所属城市id
        ,max(end_city_name) as end_city_name --末端网点所属城市名称
        ,max(end_area_id) as end_area_id --末端网点所属区县id
        ,max(end_area_name) as end_area_name --末端网点所属区县名称
        ,end_center_code --末端发件中心/集散编码
        ,max(end_center_name) as end_center_name --末端发件中心/集散名称
        ,end_franchisee_code --末端网点所属加盟商编码
        ,max(end_franchisee_name) as end_franchisee_name --末端网点所属加盟商名称
        ,final_sign_network_code --最终签收派件网点编码
        ,max(final_sign_network_name) as final_sign_network_name --最终签收派件网点名称
        ,max(planned_delivery_time) as planned_delivery_time --规划发件时间
        ,max(deadline_signing_time) as deadline_signing_time --规划签收截止时间
        ,max(span_days) as span_days --加时天数
        ,deliver_shift --派件派仓班次
        ,final_sign_user_code --最后签收派件员编码
        ,max(final_sign_user_name) as final_sign_user_name --最后签收派件员名称
        ,sum(1) as need_sign_count --应签收汇总
        ,sum(case when aging_sign_count_type = 11 then 1 else 0 end) as aging_sign_in_time_network --时效签收准点-网点签收
        ,sum(case when aging_sign_count_type = 12 then 1 else 0 end) as aging_sign_in_time_terminal_pdd --时效签收准点-终端入库(桃花岛认证)
        ,sum(case when aging_sign_count_type = 13 then 1 else 0 end) as aging_sign_in_time_terminal_other --时效签收准点-终端入库(其他)
        ,sum(case when aging_sign_count_type = 14 then 1 else 0 end) as aging_sign_in_time_proxy --时效签收准点-代理点收入
        ,sum(case when aging_sign_count_type = 15 then 1 else 0 end) as aging_sign_in_time_transfer --时效签收准点-转邮
        ,sum(case when aging_sign_count_type in (11,12,13,14,15) then 1 else 0 end) as aging_sign_in_time_count --时效签收准点-汇总
        ,sum(case when aging_sign_count_type = 21 and date_format(aging_sign_time, 'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_aging_sign_over_time_network --时效签收延误-网点签收
        ,sum(case when aging_sign_count_type = 22 and date_format(aging_sign_time, 'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_aging_sign_over_time_terminal_pdd --时效签收延误-终端入库(桃花岛认证)
        ,sum(case when aging_sign_count_type = 23 and date_format(aging_sign_time, 'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_aging_sign_over_time_terminal_other --时效签收延误-终端入库(其他)
        ,sum(case when aging_sign_count_type = 24 and date_format(aging_sign_time, 'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_aging_sign_over_time_proxy --时效签收延误-代理点收入
        ,sum(case when aging_sign_count_type = 25 and date_format(aging_sign_time, 'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_aging_sign_over_time_difficult --时效签收延误-问题件
        ,sum(case when aging_sign_count_type = 26 and date_format(aging_sign_time, 'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_aging_sign_over_time_other --时效签收延误-其他
        ,sum(case when aging_sign_count_type = 27 and date_format(aging_sign_time, 'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_aging_sign_over_time_transfer --时效签收延误-转邮
        ,sum(case when aging_sign_count_type in (21,22,23,24,27) and date_format(aging_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_aging_sign_over_time_sign_count --时效签收延误-已签收汇总
        ,sum(case when aging_sign_count_type in (25,26) and date_format(aging_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end) as 22_aging_sign_over_time_nosign_count --时效签收延误-未签收汇总
        ,sum(case when aging_sign_count_type = 21 and (date_format(aging_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(aging_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' <= to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 24_aging_sign_over_time_network --时效签收延误-网点签收
        ,sum(case when aging_sign_count_type = 22 and (date_format(aging_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(aging_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' <= to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 24_aging_sign_over_time_terminal_pdd --时效签收延误-终端入库(桃花岛认证)
        ,sum(case when aging_sign_count_type = 23 and (date_format(aging_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(aging_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' <= to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 24_aging_sign_over_time_terminal_other --时效签收延误-终端入库(其他)
        ,sum(case when aging_sign_count_type = 24 and (date_format(aging_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(aging_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' <= to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 24_aging_sign_over_time_proxy --时效签收延误-代理点收入
        ,sum(case when aging_sign_count_type = 25 and (date_format(aging_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(aging_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' <= to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 24_aging_sign_over_time_difficult --时效签收延误-问题件
        ,sum(case when aging_sign_count_type = 26 and (date_format(aging_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(aging_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' <= to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 24_aging_sign_over_time_other --时效签收延误-其他
        ,sum(case when aging_sign_count_type = 27 and (date_format(aging_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(aging_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' <= to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 24_aging_sign_over_time_transfer --时效签收延误-转邮
        ,sum(case when aging_sign_count_type in (21,22,23,24,27) and (date_format(aging_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(aging_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' <= to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 24_aging_sign_over_time_sign_count --时效签收延误-已签收汇总
        ,sum(case when aging_sign_count_type in (25,26) and (date_format(aging_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(aging_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' <= to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 24_aging_sign_over_time_nosign_count --时效签收延误-未签收汇总
        ,sum(case when aging_sign_count_type = 21 and (to_date(aging_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' > to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 00_aging_sign_over_time_network --时效签收延误-网点签收
        ,sum(case when aging_sign_count_type = 22 and (to_date(aging_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' > to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 00_aging_sign_over_time_terminal_pdd --时效签收延误-终端入库(桃花岛认证)
        ,sum(case when aging_sign_count_type = 23 and (to_date(aging_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' > to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 00_aging_sign_over_time_terminal_other --时效签收延误-终端入库(其他)
        ,sum(case when aging_sign_count_type = 24 and (to_date(aging_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' > to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 00_aging_sign_over_time_proxy --时效签收延误-代理点收入
        ,sum(case when aging_sign_count_type = 25 and (to_date(aging_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' > to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 00_aging_sign_over_time_difficult --时效签收延误-问题件
        ,sum(case when aging_sign_count_type = 26 and (to_date(aging_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' > to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 00_aging_sign_over_time_other --时效签收延误-其他
        ,sum(case when aging_sign_count_type = 27 and (to_date(aging_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' > to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 00_aging_sign_over_time_transfer --时效签收延误-转邮
        ,sum(case when aging_sign_count_type in (21,22,23,24,27) and (to_date(aging_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' > to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 00_aging_sign_over_time_sign_count --时效签收延误-已签收汇总
        ,sum(case when aging_sign_count_type in (25,26) and (to_date(aging_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' > to_date(final_plan_sign_time) and aging_sign_time is null)) then 1 else 0 end) as 00_aging_sign_over_time_nosign_count --时效签收延误-未签收汇总
        ,sum(case when is_aging_sign_24 = 1 then 1 else 0 end) as aging_sign_24_count --时效签收-24点前签收汇总
        ,sum(case when actual_sign_count_type = 11 then 1 else 0 end) as actual_sign_in_time_network --实际签收准点-网点签收
        ,sum(case when actual_sign_count_type = 12 then 1 else 0 end) as actual_sign_in_time_terminal --实际签收准点-驿站/快递柜
        ,sum(case when actual_sign_count_type = 13 then 1 else 0 end) as actual_sign_in_time_proxy --实际签收准点-代理点收入
        ,sum(case when actual_sign_count_type in (11,12,13) then 1 else 0 end) as actual_sign_in_time_count --实际签收准点-汇总
        ,sum(case when actual_sign_count_type = 21 then 1 else 0 end) as actual_sign_over_time_network --实际签收延误-网点签收
        ,sum(case when actual_sign_count_type = 22 then 1 else 0 end) as actual_sign_over_time_terminal --实际签收延误-驿站/快递柜
        ,sum(case when actual_sign_count_type = 23 then 1 else 0 end) as actual_sign_over_time_proxy --实际签收延误-代理点收入
        ,sum(case when actual_sign_count_type = 24 then 1 else 0 end) as actual_sign_over_time_difficult --实际签收延误-问题件
        ,sum(case when actual_sign_count_type = 25 then 1 else 0 end) as actual_sign_over_time_other --实际签收延误-其他
        ,sum(case when actual_sign_count_type in (21,22,23) then 1 else 0 end) as actual_sign_over_time_sign_count --实际签收延误-已签收汇总
        ,sum(case when actual_sign_count_type in (24,25) then 1 else 0 end) as actual_sign_over_time_nosign_count --实际签收延误-未签收汇总
        ,sum(case when is_actual_sign_24 = 1 then 1 else 0 end) as actual_sign_24_count --实际签收-24点前签收汇总
        --,final_plan_sign_date --规划签收日期
        ,sum(case when is_aging_sign_00 = 1 then 1 else 0 end) as aging_sign_00_count --时效签收-24点前签收汇总
        ,sum(case when is_aging_sign_22 = 1 then 1 else 0 end) as aging_sign_22_count --时效签收-24点前签收汇总
        ,min(is_common_network) as is_common_network --是否共配网点
        ,dt as end_center_send_date --末端中心/集散发件日期
        ,express_type_code    --产品类型
        ,max(express_type_name) as express_type_name
       --新增实际签收未维度  22前点汇总
       ,sum(case when actual_sign_count_type = 11 and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)           as 22_actual_sign_in_time_network     --22点前实际签收准点-网点签收
       ,sum(case when actual_sign_count_type = 12 and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)           as 22_actual_sign_in_time_terminal    --22点前实际签收准点-驿站/快递柜
       ,sum(case when actual_sign_count_type = 13 and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)           as 22_actual_sign_in_time_proxy       --22点前实际签收准点-代理点收入
       ,sum(case when actual_sign_count_type in (11,12,13) and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)  as 22_actual_sign_in_time_count       --22点前实际签收准点-汇总
       ,sum(case when actual_sign_count_type = 21 and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)           as 22_actual_sign_over_time_network   --22点前实际签收延误-网点签收
       ,sum(case when actual_sign_count_type = 22 and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)           as 22_actual_sign_over_time_terminal  --22点前实际签收延误-驿站/快递柜
       ,sum(case when actual_sign_count_type = 23 and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)           as 22_actual_sign_over_time_proxy     --22点前实际签收延误-代理点收入
       ,sum(case when actual_sign_count_type = 24 and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)           as 22_actual_sign_over_time_difficult --22点前实际签收延误-问题件
       ,sum(case when actual_sign_count_type = 25 and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)           as 22_actual_sign_over_time_other     --22点前实际签收延误-其他
       ,sum(case when actual_sign_count_type in (21,22,23) and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)  as 22_actual_sign_over_time_sign_count --22点前实际签收延误-已签收汇总
       ,sum(case when actual_sign_count_type in (24,25) and date_format(actual_sign_time,'yyyy-MM-dd HH')< concat(to_date(final_plan_sign_time),' 22') then 1 else 0 end)     as 22_actual_sign_over_time_nosign_count --22点前实际签收延误-未签收汇总
       --新增实际签收未维度  22-24点汇总
       ,sum(case when actual_sign_count_type = 11 and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' <= to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)         as 24_actual_sign_in_time_network     --22-24点实际签收准点-网点签收
       ,sum(case when actual_sign_count_type = 12 and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' <= to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)         as 24_actual_sign_in_time_terminal    --22-24点实际签收准点-驿站/快递柜
       ,sum(case when actual_sign_count_type = 13 and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' <= to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)         as 24_actual_sign_in_time_proxy       --22-24点实际签收准点-代理点收入
       ,sum(case when actual_sign_count_type in (11,12,13) and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' <= to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end) as 24_actual_sign_in_time_count       --22-24点实际签收准点-汇总
       ,sum(case when actual_sign_count_type = 21 and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' <= to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)         as 24_actual_sign_over_time_network   --22-24点实际签收延误-网点签收
       ,sum(case when actual_sign_count_type = 22 and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' <= to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)         as 24_actual_sign_over_time_terminal  --22-24点实际签收延误-驿站/快递柜
       ,sum(case when actual_sign_count_type = 23 and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' <= to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)         as 24_actual_sign_over_time_proxy     --22-24点实际签收延误-代理点收入
       ,sum(case when actual_sign_count_type = 24 and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' <= to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)         as 24_actual_sign_over_time_difficult --22-24点实际签收延误-问题件
       ,sum(case when actual_sign_count_type = 25 and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' <= to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)         as 24_actual_sign_over_time_other     --22-24点实际签收延误-其他
       ,sum(case when actual_sign_count_type in (21,22,23) and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' <= to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end) as 24_actual_sign_over_time_sign_count --22-24点实际签收延误-已签收汇总
       ,sum(case when actual_sign_count_type in (24,25)    and (date_format(actual_sign_time,'yyyy-MM-dd HH')>=concat(to_date(final_plan_sign_time),' 22') and to_date(actual_sign_time)<date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' <= to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)    as 24_actual_sign_over_time_nosign_count--22-24实际签收延误-未签收汇总
       --新增实际签收未维度  超24点汇总
       ,sum(case when actual_sign_count_type = 11  and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' > to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)         as 00_actual_sign_in_time_network     --超24点实际签收准点-网点签收
       ,sum(case when actual_sign_count_type = 12 and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' > to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)          as 00_actual_sign_in_time_terminal    --超24点实际签收准点-驿站/快递柜
       ,sum(case when actual_sign_count_type = 13 and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' > to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)          as 00_actual_sign_in_time_proxy       --超24点实际签收准点-代理点收入
       ,sum(case when actual_sign_count_type in (11,12,13) and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' > to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end) as 00_actual_sign_in_time_count       --超24点实际签收准点-汇总
       ,sum(case when actual_sign_count_type = 21 and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' > to_date(final_plan_sign_time) and actual_sign_time is null))  then 1 else 0 end)         as 00_actual_sign_over_time_network   --超24点实际签收延误-网点签收
       ,sum(case when actual_sign_count_type = 22 and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' > to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)          as 00_actual_sign_over_time_terminal  --超24点实际签收延误-驿站/快递柜
       ,sum(case when actual_sign_count_type = 23 and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' > to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)          as 00_actual_sign_over_time_proxy     --超24点实际签收延误-代理点收入
       ,sum(case when actual_sign_count_type = 24 and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' > to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)          as 00_actual_sign_over_time_difficult --超24点实际签收延误-问题件
       ,sum(case when actual_sign_count_type = 25 and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' > to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)          as 00_actual_sign_over_time_other     --超24点实际签收延误-其他
       ,sum(case when actual_sign_count_type in (21,22,23) and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' != to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end) as 00_actual_sign_over_time_sign_count --超24点实际签收延误-已签收汇总
       ,sum(case when actual_sign_count_type in (24,25) and (to_date(actual_sign_time)>=date_add(final_plan_sign_time,1) or ('{{ execution_date | cst_ds }}' != to_date(final_plan_sign_time) and actual_sign_time is null)) then 1 else 0 end)    as 00_actual_sign_over_time_nosign_count--超24点实际签收延误-未签收汇总
       ,sum(if( actual_sign_time < concat_ws(' ',date_format(final_plan_sign_time, 'yyyy-MM-dd'),'22:00:00'),1,0)) as actual_sign_22_count  -- 22点签收量
       ,sum(if( actual_sign_time < concat_ws(' ',date_format(final_plan_sign_time, 'yyyy-MM-dd'),'23:59:59'),1,0)) as actual_sign_00_count  --24点前前签收量
       ,third_code  --三段码code
       ,max(third_name) as third_name --三段码名字
       ,max(end_brand_code) as end_brand_code
       ,max(end_brand_name) as end_brand_name
        ,dt
from (
      select t.*,
             first_value(is_common) over (partition by end_center_send_date,final_sign_network_code order by is_common asc nulls last) is_common_network
      from jms_dm.dm_terminal_sign_detail_new_dt t
      where dt between date_format(date_add('{{ execution_date | cst_ds }}', -14), 'yyyy-MM-dd') and date_format(date_add('{{ execution_date | cst_ds }}', 0), 'yyyy-MM-dd')
) t
group by dt,
         order_source_name,
         goods_type_code,
         end_manage_region_code,
         end_agent_code,
         end_center_code,
         end_franchisee_code,
         final_sign_network_code,
         deliver_shift,
         final_sign_user_code,
         express_type_code,
         third_code
 distribute by dt,abs(hash(final_sign_user_code)) % 10
 ;